﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using System.ServiceModel;
using WickedSick.CommonComponents.Music;

namespace WickedSick.ServerComponents.Music
{
    /* a class which implements the interface (see SDBService),
     * and configuration entries that specify behaviors associated with 
     * that implementation (see <system.serviceModel> in web.config)*/

    [ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]
    [KnownType(typeof(ChunkData<SongData>))]
    [KnownType(typeof(ChunkData<AlbumData>))]
    [KnownType(typeof(ChunkData<ArtistData>))]
    [KnownType(typeof(ChunkData<AuditData>))]
    [KnownType(typeof(ChunkData<PlaylistData>))]
    [KnownType(typeof(List<int>))]
    [KnownType(typeof(Guid))]
    public class SDBService : ISDBService
    {
        private SqlConnection db;
        private List<ChunkData<SongData>> _SongChunks;
        private List<ChunkData<AlbumData>> _AlbumChunks;
        private List<ChunkData<ArtistData>> _ArtistChunks;

        private DateTime _LastSingleChangeChunk;
        private ChunkData<AuditData> _SingleChangeChunk;
        private Dictionary<Guid, List<ChunkData<AuditData>>> _CurrentChangeRequests;

        private Dictionary<Guid, List<ChunkData<PlaylistData>>> _CurrentPlaylists;

        private Dictionary<Guid, int> _MemberIDs;

        public SDBService()
        {
            PreloadData();

            _CurrentChangeRequests = new Dictionary<Guid, List<ChunkData<AuditData>>>();
            PreloadChanges();

            _CurrentPlaylists = new Dictionary<Guid, List<ChunkData<PlaylistData>>>();

            _MemberIDs = new Dictionary<Guid, int>();
        }

        private void OpenConnection()
        {
            try
            {
                db.Open();
            }
            catch (Exception) { }
        }

        private void CloseConnection()
        {
            try
            {
                db.Close();
            }
            catch (Exception) { }
        }

        private void PreloadData()
        {
            int max_chunk_size = Convert.ToInt32(ConfigurationManager.AppSettings["MAX_CHUNK_SIZE"]);
            db = new SqlConnection(ConfigurationManager.ConnectionStrings["SDB"].ConnectionString);

            /// Build song data chunks
            {
                ChunkData<SongData> sdChunk = new ChunkData<SongData>();
                // Load mass song data chunk
                SqlCommand cmdSongs = new SqlCommand("GetAllSongs", db);
                cmdSongs.CommandType = CommandType.StoredProcedure;
                OpenConnection();
                SqlDataReader sdrSongs = cmdSongs.ExecuteReader();
                while (sdrSongs.Read())
                {
                    SongData curData = SongData.FromDataReader(sdrSongs);
                    sdChunk.Chunk.Add(curData.SongID, curData);
                }
                sdrSongs.Close();
                CloseConnection();
                //Split the chunk and save away to cache
                sdChunk.MaxChunkSize = max_chunk_size;
                _SongChunks = sdChunk.Split();
            }

            /// Build album data chunks
            {
                ChunkData<AlbumData> adChunk = new ChunkData<AlbumData>();
                //Load mass album data chunk
                SqlCommand cmdAlbums = new SqlCommand("GetAllAlbums", db);
                cmdAlbums.CommandType = CommandType.StoredProcedure;
                OpenConnection();
                SqlDataReader sdrAlbums = cmdAlbums.ExecuteReader();
                while (sdrAlbums.Read())
                {
                    AlbumData curData = AlbumData.FromDataReader(sdrAlbums);
                    adChunk.Chunk.Add(curData.AlbumID, curData);
                }
                sdrAlbums.Close();
                CloseConnection();

                foreach (int k in adChunk.Chunk.Keys)
                {
                    AlbumData album_data = adChunk.Chunk[k];
                    SqlCommand cmdGetSongIDs = new SqlCommand("GetSongIDsFromAlbum", db);
                    cmdGetSongIDs.CommandType = CommandType.StoredProcedure;
                    cmdGetSongIDs.Parameters.AddWithValue("AlbumID", k);

                    OpenConnection();
                    SqlDataReader sdrGetSongIDs = cmdGetSongIDs.ExecuteReader();
                    while (sdrGetSongIDs.Read())
                    {
                        int sid = Convert.ToInt32(sdrGetSongIDs["SONG_ID"]);
                        album_data.SongIDs.Add(sid);
                    }
                    sdrGetSongIDs.Close();
                    CloseConnection();
                }

                adChunk.MaxChunkSize = max_chunk_size;
                _AlbumChunks = adChunk.Split();
            }

            /// Build artist data chunks
            {
                ChunkData<ArtistData> arChunk = new ChunkData<ArtistData>();
                //Load mass artist data chunk
                SqlCommand cmdArtists = new SqlCommand("GetAllArtists", db);
                cmdArtists.CommandType = CommandType.StoredProcedure;
                OpenConnection();
                SqlDataReader sdrArtists = cmdArtists.ExecuteReader();
                while (sdrArtists.Read())
                {
                    ArtistData curData = ArtistData.FromDataReader(sdrArtists);
                    arChunk.Chunk.Add(curData.ArtistID, curData);
                }
                sdrArtists.Close();
                CloseConnection();

                foreach (int k in arChunk.Chunk.Keys)
                {
                    ArtistData artist_data = arChunk.Chunk[k];
                    SqlCommand cmdGetAlbumIDs = new SqlCommand("GetAlbumIDsFromArtist", db);
                    cmdGetAlbumIDs.CommandType = CommandType.StoredProcedure;
                    cmdGetAlbumIDs.Parameters.AddWithValue("ArtistID", k);

                    OpenConnection();
                    SqlDataReader sdrGetAlbumIDs = cmdGetAlbumIDs.ExecuteReader();
                    while (sdrGetAlbumIDs.Read())
                    {
                        int aid = Convert.ToInt32(sdrGetAlbumIDs["ALBUM_ID"]);
                        artist_data.AlbumIDs.Add(aid);
                    }
                    sdrGetAlbumIDs.Close();
                    CloseConnection();
                }

                arChunk.MaxChunkSize = max_chunk_size;
                _ArtistChunks = arChunk.Split();
            }
        }

        private void PreloadChanges()
        {
            db = new SqlConnection(ConfigurationManager.ConnectionStrings["SDB"].ConnectionString);
            ///Build change data chunk
            {
                ChunkData<AuditData> adChunk = new ChunkData<AuditData>();
                //Load mass audit data chunk
                SqlCommand cmdAudits = new SqlCommand("GetAllAudits", db);
                cmdAudits.CommandType = CommandType.StoredProcedure;
                OpenConnection();
                SqlDataReader sdrAudits = cmdAudits.ExecuteReader();
                while (sdrAudits.Read())
                {
                    AuditData curData = AuditData.FromDataReader(sdrAudits);
                    adChunk.Chunk.Add(curData.AuditID, curData);
                }
                sdrAudits.Close();
                CloseConnection();

                _SingleChangeChunk = adChunk;
            }
            _LastSingleChangeChunk = DateTime.Now;
        }

        private Guid GetChanges(DateTime LastUpdate)
        {
            int max_chunk_size = Convert.ToInt32(ConfigurationManager.AppSettings["MAX_CHUNK_SIZE"]);
            db = new SqlConnection(ConfigurationManager.ConnectionStrings["SDB"].ConnectionString);

            AssertNoNewChanges();
            ChunkData<AuditData> temp = RebuildChunkWithLatest(LastUpdate);
            temp.MaxChunkSize = max_chunk_size;
            List<ChunkData<AuditData>> temp2 = temp.Split();
            _CurrentChangeRequests.Add(temp2[0].ID, temp2);
            return temp2[0].ID;
        }

        private void AssertNoNewChanges()
        {
            db = new SqlConnection(ConfigurationManager.ConnectionStrings["SDB"].ConnectionString);

            SqlCommand cmd = new SqlCommand("NumberOfAuditChanges", db);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("LastUpdate", _LastSingleChangeChunk);

            //check for new updates
            OpenConnection();
            int temp = Convert.ToInt32(cmd.ExecuteScalar());
            CloseConnection();
            //if new updates, change single change chunk
            if (temp > 0)
            {
                PreloadChanges();
            }
        }

        private ChunkData<AuditData> RebuildChunkWithLatest(DateTime LastUpdate)
        {
            ChunkData<AuditData> temp = new ChunkData<AuditData>();
            foreach (int k in _SingleChangeChunk.Chunk.Keys)
            {
                AuditData ad = _SingleChangeChunk.Chunk[k];
                if ((new DateTime(ad.AuditDate)).CompareTo(LastUpdate) > 0)
                {
                    temp.Chunk.Add(ad.AuditID, new AuditData(ad));
                }
            }
            return temp;
        }

        private Guid GetPlaylists(int MemberID)
        {
            int max_chunk_size = Convert.ToInt32(ConfigurationManager.AppSettings["MAX_CHUNK_SIZE"]);
            db = new SqlConnection(ConfigurationManager.ConnectionStrings["SDB"].ConnectionString);
            ///Build playlist data chunk
            {
                SqlCommand cmdPlaylists = new SqlCommand("GetMemberPlaylists", db);
                cmdPlaylists.CommandType = CommandType.StoredProcedure;
                cmdPlaylists.Parameters.AddWithValue("MemberID", MemberID);

                ChunkData<PlaylistData> curChunk = new ChunkData<PlaylistData>();

                OpenConnection();
                SqlDataReader sdrPlaylists = cmdPlaylists.ExecuteReader();
                while (sdrPlaylists.Read())
                {
                    PlaylistData pd = PlaylistData.FromDataReader(sdrPlaylists);
                    curChunk.Chunk.Add(pd.PlaylistID, pd);
                }
                sdrPlaylists.Close();
                CloseConnection();

                foreach (int k in curChunk.Chunk.Keys)
                {
                    PlaylistData pd = curChunk.Chunk[k];
                    SqlCommand cmdSongs = new SqlCommand("GetPlaylistSongIDs", db);
                    cmdSongs.CommandType = CommandType.StoredProcedure;
                    cmdSongs.Parameters.AddWithValue("PlaylistID", pd.PlaylistID);

                    OpenConnection();
                    SqlDataReader sdrSongs = cmdSongs.ExecuteReader();
                    while (sdrSongs.Read())
                    {
                        pd.SongIDs.Add(Convert.ToInt32(sdrSongs["SongID"]));
                    }
                    sdrSongs.Close();
                    CloseConnection();
                }
                curChunk.MaxChunkSize = max_chunk_size;
                List<ChunkData<PlaylistData>> allChunks = curChunk.Split();
                _CurrentPlaylists.Add(allChunks[0].ID, allChunks);
                return allChunks[0].ID;
            }
        }

        private bool ValidateMember(Guid id, out int MemberID)
        {
            MemberID = -1;
            if (_MemberIDs.ContainsKey(id))
            {
                MemberID = _MemberIDs[id];
                return true;
            }
            return false;
        }

        #region ISDBService Members

        public ChunkData<SongData> RequestAllSongs()
        {
            if (_SongChunks != null && _SongChunks.Count > 0)
            {
                return _SongChunks[0];
            }
            else
            {
                throw new Exception("No song chunks have been loaded.");
            }
        }

        public ChunkData<SongData> RequestNextSongChunk(int ChunkNumber)
        {
            if (_SongChunks != null && _SongChunks.Count > ChunkNumber)
            {
                return _SongChunks[ChunkNumber];
            }
            else
            {
                throw new Exception("Song chunk is out of bounds.");
            }
        }

        public ChunkData<AlbumData> RequestAllAlbums()
        {
            if (_AlbumChunks != null && _AlbumChunks.Count > 0)
            {
                return _AlbumChunks[0];
            }
            else
            {
                throw new Exception("No album chunks have been loaded.");
            }
        }

        public ChunkData<AlbumData> RequestNextAlbumChunk(int ChunkNumber)
        {
            if (_AlbumChunks != null && _AlbumChunks.Count > ChunkNumber)
            {
                return _AlbumChunks[ChunkNumber];
            }
            else
            {
                throw new Exception("Album chunk is out of bounds.");
            }
        }

        public ChunkData<ArtistData> RequestAllArtists()
        {
            if (_ArtistChunks != null && _ArtistChunks.Count > 0)
            {
                return _ArtistChunks[0];
            }
            else
            {
                throw new Exception("No artist chunks have been loaded.");
            }
        }

        public ChunkData<ArtistData> RequestNextArtistChunk(int ChunkNumber)
        {
            if (_ArtistChunks != null && _ArtistChunks.Count > ChunkNumber)
            {
                return _ArtistChunks[ChunkNumber];
            }
            else
            {
                throw new Exception("Artist chunk is out of bounds.");
            }
        }

        public ChunkData<AuditData> RequestChanges(DateTime LastUpdate)
        {
            Guid new_guid = GetChanges(LastUpdate);
            if (_CurrentChangeRequests[new_guid].Count > 0)
            {
                return _CurrentChangeRequests[new_guid][0];
            }
            else
            {
                throw new Exception("No change chunks loaded.");
            }
        }

        public ChunkData<AuditData> RequestNextChangesChunk(Guid id, int ChunkNumber)
        {
            ChunkData<AuditData> temp;
            if (_CurrentChangeRequests.ContainsKey(id) && _CurrentChangeRequests[id].Count > ChunkNumber)
            {
                temp = _CurrentChangeRequests[id][ChunkNumber];
                if ((temp.ChunkNumber + 1) >= temp.TotalChunks)
                {
                    _CurrentChangeRequests.Remove(id);
                }
            }
            else
            {
                throw new Exception("Chunk Number is out of range or change chunks have not been loaded.");
            }
            return temp;
        }

        public ChunkData<PlaylistData> RequestAllPlaylists(int MemberID)
        {
            Guid new_guid = GetPlaylists(MemberID);
            if (_CurrentPlaylists[new_guid].Count > 0)
            {
                return _CurrentPlaylists[new_guid][0];
            }
            else
            {
                throw new Exception("No playlist chunks loaded.");
            }
        }

        public ChunkData<PlaylistData> RequestNextPlaylistChunk(Guid id, int ChunkNumber)
        {
            ChunkData<PlaylistData> temp;
            if (_CurrentPlaylists.ContainsKey(id) && _CurrentPlaylists[id].Count > ChunkNumber)
            {
                temp = _CurrentPlaylists[id][ChunkNumber];
                if ((temp.ChunkNumber + 1) >= temp.TotalChunks)
                {
                    _CurrentPlaylists.Remove(id);
                }
            }
            else
            {
                throw new Exception("Chunk Number is out of range or playlist chunks have not been loaded.");
            }
            return temp;
        }

        public List<int> RequestLibraryIDs(int MemberID)
        {
            List<int> temp = new List<int>();
            SqlCommand cmd = new SqlCommand("GetLibrarySongIDs", db);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("MemberID", MemberID);

            OpenConnection();
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
                temp.Add(Convert.ToInt32(sdr["SongID"]));
            }
            CloseConnection();

            return temp;
        }

        public Guid AuthenticateMember(string Login, string Password)
        {
            db = new SqlConnection(ConfigurationManager.ConnectionStrings["SDB"].ConnectionString);

            SqlCommand cmd = new SqlCommand("AuthenticateMember", db);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("Login", Login);
            cmd.Parameters.AddWithValue("Password", Password);

            OpenConnection();
            int temp = Convert.ToInt32(cmd.ExecuteScalar());
            CloseConnection();

            Guid temp2 = Guid.Empty;
            if (temp > -1)
            {
                do
                {
                    temp2 = Guid.NewGuid();
                } while (_MemberIDs.ContainsKey(temp2));

                _MemberIDs.Add(temp2, temp);
            }

            return temp2;
        }

        public bool LogoffMember(Guid id)
        {
            int MemberID;
            if (ValidateMember(id, out MemberID))
            {
                _MemberIDs.Remove(id);
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool MoveSongToLibrary(Guid id, int SongID)
        {
            int MemberID;
            if (!ValidateMember(id, out MemberID))
            {
                return false;
            }

            db = new SqlConnection(ConfigurationManager.ConnectionStrings["SDB"].ConnectionString);

            SqlCommand cmd = new SqlCommand("LibrarySong", db);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("SongID", SongID);
            cmd.Parameters.AddWithValue("MemberID", MemberID);

            OpenConnection();
            cmd.ExecuteNonQuery();
            CloseConnection();

            return true;
        }

        public bool MoveSongToArchive(Guid id, int SongID)
        {
            int MemberID;
            if (!ValidateMember(id, out MemberID))
            {
                return false;
            }

            db = new SqlConnection(ConfigurationManager.ConnectionStrings["SDB"].ConnectionString);

            SqlCommand cmd = new SqlCommand("ArchiveSong", db);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("SongID", SongID);
            cmd.Parameters.AddWithValue("MemberID", MemberID);

            OpenConnection();
            cmd.ExecuteNonQuery();
            CloseConnection();

            return true;
        }

        public bool InitiateUploadedSong(Guid id, SongData newSong)
        {
            int MemberID;
            if (!ValidateMember(id, out MemberID))
            {
                return false;
            }

            throw new NotImplementedException();

            return true;
        }

        public bool DeletePlaylist(Guid id, int PlaylistID, DateTime TimeAffected)
        {
            int MemberID;
            if (!ValidateMember(id, out MemberID))
            {
                return false;
            }

            db = new SqlConnection(ConfigurationManager.ConnectionStrings["SDB"].ConnectionString);

            SqlCommand cmd = new SqlCommand("DeletePlaylist", db);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("PlaylistID", PlaylistID);
            cmd.Parameters.AddWithValue("MemberID", MemberID);
            cmd.Parameters.AddWithValue("ActionTime", TimeAffected);

            OpenConnection();
            // Return 0 if playlist does not belong to user
            int temp = Convert.ToInt32(cmd.ExecuteScalar());
            CloseConnection();

            return (temp > 0);
        }

        #endregion
    }
}